package com.tsky.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.sun.rowset.CachedRowSetImpl;

/**
 * CRUD for database
 * @author 196
 *
 */
public final class JdbcUtility {
  
	private static JdbcUtility instance = null;
	
	protected JdbcUtility(){}
	
	public static synchronized JdbcUtility getInstance(){
		if(instance == null){
			instance = new JdbcUtility();
		}
		return instance;
	}
	
	/**
	 * 获取一个数据库连接
	 * @param dataSourceName
	 * @return
	 */
	private static synchronized Connection getConnection(String dataSourceName){
		try {
			ComboPooledDataSource ds = DataSourceManager.getDataSource(dataSourceName);
			return ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 关闭数据库连接
	 * @param conn
	 * @param pstmt
	 * @param rs
	 */
	private static void close(Connection conn,PreparedStatement pstmt,ResultSet rs){
		try {
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
			if(conn != null){
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 执行数据库查询操作
	 * @param dsName
	 * @param sql
	 * @param params
	 * @return 查询结果的离线rowset
	 */
	public static RowSet executeQuery(String dsName,String sql,String[] params){
		Connection conn = null;
		PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet rs = null;
		CachedRowSet crs = null;
		try {
			conn = getConnection(dsName);
			pstmt = conn.prepareStatement(sql);
			stmt = conn.createStatement();
			if(params != null){
				for (int i = 0; i < params.length; i++) {
//					pstmt.setString(1+i, params[i]);
				}
			}
//			rs = pstmt.executeQuery();
			rs = stmt.executeQuery(sql);
			System.out.println("rs:"+rs.getRow());
			// 创建cacherowset
			crs = new CachedRowSetImpl();
			crs.populate(rs);
			System.out.println("crs:"+crs.getRow());
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(conn,pstmt,rs);
		}
		
		return crs;
	}
	
	public static RowSet executeQuery(String dsName,String sql,String[] params,Integer pageIndex,Integer pageSize){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		CachedRowSet crs = null;
		try {
			conn = getConnection(dsName);
			pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			if(params != null){
				for (int i = 0; i < params.length; i++) {
					pstmt.setString(1+i, params[i]);
				}
			}
			rs = pstmt.executeQuery();
			
			// 创建cacherowset
			crs = new CachedRowSetImpl();
			crs.setPageSize(pageSize);
			crs.populate(rs,(pageIndex-1)*pageSize+1);
			crs.populate(rs);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(conn,pstmt,rs);
		}
		
		return crs;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
